﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Collections;
using System.Windows.Forms;
namespace SCCM
{
    class DataBase
    {
        //private string sqlAddress = @"Data Source=(LocalDB)\v11.0;AttachDbFilename="+System.Environment.CurrentDirectory+"/SCCM.mdf;Integrated Security=True;Connect Timeout=30";
      //  private static string sqlAddress = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+System.Environment.CurrentDirectory+@"\SCCM.mdb;Persist Security Info=True";
        private static string sqlAddress = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Environment.CurrentDirectory + @"\SCCM.mdb;Persist Security Info=True";
        
        private OleDbConnection con;
        private OleDbCommand com;
        public OleDbConnection GetConnection()
        {
          
            try
            {
                con = new OleDbConnection(sqlAddress);
                con.Open();
            }
            catch (Exception ex)
            {
                StreamWriter sw2 = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw2.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw2.Close();
            }
            return con;
        }
        public void Close()
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
        public void Insert(string rdt, double no2, double co, double no, double o3, double tem, double rh, double ato, string dbz, string kgs, string kgg, int m)
        {
            com = GetConnection().CreateCommand();

            //SELECT count(*) from DataStore Where Times =#2016-02-05 00:00# and Bdzmc="1" and Kgsmc="1" and Kggbh="1";

            com.CommandText = "SELECT ID from DataStore Where Times=#"+ rdt + "# and Bdzmc='"+ dbz + "' and Kgsmc='"+kgs+"' and Kggbh='"+kgg+"'";
            try
            {
                DataTable dt = new DataTable();
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
                if (dt.Rows.Count != 0) {
                    Close();
                    return;
                }
            
               
                

            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.WriteLine(com.CommandText);
                sw.Close();
            }


            com = GetConnection().CreateCommand();
            com.CommandText = "insert into [DataStore] ([Times],[NO2],[CO],[NO],[O3],[TEM],[RH],[ATO],[Bdzmc],[Kgsmc],[Kggbh],[m]) values (@rdt,@no2,@co,@no,@o3,@tem,@rh,@ato,@bdz,@kgs,@kgg,@m)";
            com.Parameters.Add("@rdt", DbType.DateTime).Value= rdt;
            com.Parameters.AddWithValue("@no2", no2);
            com.Parameters.AddWithValue("@co", co);
            com.Parameters.AddWithValue("@no", no);
            com.Parameters.AddWithValue("@o3", o3);
            com.Parameters.AddWithValue("@tem", tem);
            com.Parameters.AddWithValue("@rh", rh);
            com.Parameters.AddWithValue("@ato", ato);
            com.Parameters.AddWithValue("@dbz", dbz);
            com.Parameters.AddWithValue("@kgs", kgs);
            com.Parameters.AddWithValue("@kgg", kgg);
            com.Parameters.AddWithValue("@m", m);

            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.WriteLine(com.CommandText);
                sw.Close();
            }
            finally
            {
                Close();
            }
        }
        public List<double> getHistory(string kid, string bdzmc, string kgsmc, string kggbh)
        {
            List<double> val = new List<double>(65535);
            com = GetConnection().CreateCommand();
            com.CommandText = "select * from DataStore where Kind=@KID and BDZMC=@BDZMC and KGSMC=@KGSMC and KGGBH=@KGGBH order by Times desc";
            com.Parameters.AddWithValue("@KID", kid);
            com.Parameters.AddWithValue("@BDZMC", bdzmc);
            com.Parameters.AddWithValue("@KGSMC", kgsmc);
            com.Parameters.AddWithValue("@KGGBH", kggbh);

            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dt.Dispose();
                foreach (DataRow dr in dt.Rows)
                {
                    val.Add(double.Parse(dr["Val"].ToString()));
                }
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return val;
        }
        public ArrayList[] getHistory2(string kid, string bdzmc, string kgsmc, string kggbh)
        {
            ArrayList[] val = new ArrayList[2];
            val[0] = new ArrayList();
            val[1] = new ArrayList();
            com = GetConnection().CreateCommand();
            com.CommandText = "select @kid,Times from DataStore where BDZMC=@BDZMC and KGSMC=@KGSMC and KGGBH=@KGGBH";
            com.Parameters.AddWithValue("@kid", kid);
            com.Parameters.AddWithValue("@BDZMC", bdzmc);
            com.Parameters.AddWithValue("@KGSMC", kgsmc);
            com.Parameters.AddWithValue("@KGGBH", kggbh);

            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dt.Dispose();
                foreach (DataRow dr in dt.Rows)
                {
                    val[0].Add(dr["@kid"]);
                    val[1].Add(dr["Times"]);
                }
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return val;
        }
        /**
         * 获取7个指标的一天内的数据，用来标准化并显示
         */
        public ArrayList[] getHistory3(DateTime rdt1, DateTime rdt2, string kid, string bdzmc, string kgsmc, string kggbh,int flag)
        {
            string sql = "select  [" + kid + "] ,  [Times] from DataStore  where (Times between " + "#" + rdt1.ToString("yyyy-MM-dd") + "# and #" + (rdt2.AddDays(1)).ToString("yyyy-MM-dd") + "# ) and BDZMC= '" + bdzmc + "' and KGSMC= '" + kgsmc + "' and KGGBH='" + kggbh + "' ";

            if (flag == 1 || flag == 0)
            {
                sql += "and m=" + flag + "  order by StoreTime asc";
            }
            else {
                sql += "  order by StoreTime asc";
            }


            ArrayList[] val = new ArrayList[2];
            val[0] = new ArrayList();
            val[1] = new ArrayList();
            com = GetConnection().CreateCommand();
            //com.CommandText = "select  [" + kid + "] ,  [Times] from DataStore  where (Times between " + "#" + rdt1.ToString("yyyy-MM-dd") + "# and #" + (rdt2.AddDays(1)).ToString("yyyy-MM-dd") + "# ) and BDZMC= '" + bdzmc + "' and KGSMC= '" + kgsmc + "' and KGGBH='" + kggbh + "' order by StoreTime asc";
            com.CommandText = sql;
            com.Parameters.Add("@rdt1", DbType.DateTime).Value = rdt1.ToString("yyyy-MM-dd HH:mm");
            com.Parameters.Add("@rdt2", DbType.DateTime).Value = rdt2.ToString("yyyy-MM-dd HH:mm");
       


            com.Parameters.AddWithValue("@BDZMC", bdzmc);
            com.Parameters.AddWithValue("@KGSMC", kgsmc);
            com.Parameters.AddWithValue("@KGGBH", kggbh);

            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                DataTable dt = new DataTable();
                sda.Fill(dt);
               dt.Dispose();
                foreach (DataRow dr in dt.Rows)
                {
                    val[0].Add(dr[0]);
                    val[1].Add(Convert.ToDateTime(dr["Times"]).ToString("yyyy-MM-dd HH:mm").Replace(" ","\n"));
                }
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString()+"-----------"+ex.StackTrace);
                sw.Close();
            }
            finally
            {
                Close();
            }
            return val;
        }
        /**
         * 新的历史数据，提供给表格使用
         */
        public DataTable GetDataTableForHistoryForm(DateTime rdt1,DateTime  rdt2 ,string bdzmc, string kgsmc, string kggbh, int flag)
        {
            DataTable dt = new DataTable();
            string sql = "select * from DataStore where (Times between " + "#" + rdt1.ToString("yyyy-MM-dd") + "# and #" + (rdt2.AddDays(1)).ToString("yyyy-MM-dd") + "# ) and BDZMC= '" + bdzmc + "' and KGSMC= '" + kgsmc + "' and KGGBH='" + kggbh + "' ";
            if (flag == 1 || flag == 0)
            {
                sql += "and m=" + flag + "  order by StoreTime desc";
            }
            else {
                sql += "  order by StoreTime desc";
            }
            com = GetConnection().CreateCommand();
            com.CommandText = sql;
            //com.CommandText = "select * from DataStore where year(Times)>=year(@rdt1)  and month(Times)>=month(@rdt1)  and day(Times)>=day(@rdt1) and  year(Times)<=year(@rdt2) and month(Times)<=month(@rdt2)  and day(Times)<=day(@rdt2) and BDZMC=@BDZMC and KGSMC=@KGSMC and KGGBH=@KGGBH order by StoreTime desc";

            //com.CommandText = "select * from DataStore where (Times between " + "#" + rdt1.ToString("yyyy-MM-dd") + "# and #" + (rdt2.AddDays(1)).ToString("yyyy-MM-dd") + "# ) and BDZMC= '"+ bdzmc+"' and KGSMC= '"+kgsmc+"' and KGGBH='"+kggbh+ "' order by StoreTime desc";
            
            
            //com.Parameters.Add("@rdt1", DbType.DateTime).Value = rdt1.ToString("yyyy-MM-dd");
            //com.Parameters.Add("@rdt2", DbType.DateTime).Value = rdt2.ToString("yyyy-MM-dd");
            //com.Parameters.AddWithValue("@BDZMC", bdzmc);
            //com.Parameters.AddWithValue("@KGSMC", kgsmc);
            //com.Parameters.AddWithValue("@KGGBH", kggbh);
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
               
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(dt);
                sw.Close();
            }
            finally
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(dt);
                sw.Close();
                Close();
            }
            return dt;
        }
        public DataTable GetAllHistoryForm()
        {
            DataTable dt = new DataTable();
            string sql = "select * from DataStore";
            com = GetConnection().CreateCommand();
            com.CommandText = sql;
            //com.CommandText = "select * from DataStore where year(Times)>=year(@rdt1)  and month(Times)>=month(@rdt1)  and day(Times)>=day(@rdt1) and  year(Times)<=year(@rdt2) and month(Times)<=month(@rdt2)  and day(Times)<=day(@rdt2) and BDZMC=@BDZMC and KGSMC=@KGSMC and KGGBH=@KGGBH order by StoreTime desc";

            //com.CommandText = "select * from DataStore where (Times between " + "#" + rdt1.ToString("yyyy-MM-dd") + "# and #" + (rdt2.AddDays(1)).ToString("yyyy-MM-dd") + "# ) and BDZMC= '"+ bdzmc+"' and KGSMC= '"+kgsmc+"' and KGGBH='"+kggbh+ "' order by StoreTime desc";


            //com.Parameters.Add("@rdt1", DbType.DateTime).Value = rdt1.ToString("yyyy-MM-dd");
            //com.Parameters.Add("@rdt2", DbType.DateTime).Value = rdt2.ToString("yyyy-MM-dd");
            //com.Parameters.AddWithValue("@BDZMC", bdzmc);
            //com.Parameters.AddWithValue("@KGSMC", kgsmc);
            //com.Parameters.AddWithValue("@KGGBH", kggbh);
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);

                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(dt);
                sw.Close();
            }
            finally
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(dt);
                sw.Close();
                Close();
            }
            return dt;
        }
        //获取所有的变电站信息
        //根据变电站名称获取开关室信息
        public DataTable getAllBianDianZhan()
        {
            DataTable dt = new DataTable();
            com = GetConnection().CreateCommand();
            com.CommandText = "select distinct(Bdzmc) from [PositionInfo]";
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return dt;
        }
        //根据变电站获取开关室列表
        public DataTable getAllKaiGuanShiByBDZ(string bdz)
        {
            DataTable dt = new DataTable();
            com = GetConnection().CreateCommand();
            com.CommandText = "select distinct(Kgsmc) from [PositionInfo] where Bdzmc=@Bdzmc";
            com.Parameters.AddWithValue("@Bdzmc", bdz);
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return dt;
        }
        //根据开关室和变电站获取开关编号
        public DataTable getAllKaiGuanGuiByBdzAndKgs(string bdz,string kgs)
        {
            DataTable dt = new DataTable();
            com = GetConnection().CreateCommand();
            com.CommandText = "select distinct(Kggbh) from [PositionInfo] where Bdzmc=@Bdzmc and Kgsmc=@Kgsmc";
            com.Parameters.AddWithValue("@Bdzmc", bdz);
            com.Parameters.AddWithValue("@Kgsmc", kgs);
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory+"\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return dt;
        }

        public string[] getNamesByNumbers(string bdz, string kgs,string kgg)
        {
            DataTable dt = new DataTable();
            com = GetConnection().CreateCommand();
            com.CommandText = "select Bdzmc,Kgsmc,Kggbh from [PositionInfo] where Bdzbh=@Bdzbh and Kgsbh=@Kgsbh and Kggbh0=@Kggbh0";
            com.Parameters.AddWithValue("@Bdzbh", bdz);
            com.Parameters.AddWithValue("@Kgsbh", kgs);
            com.Parameters.AddWithValue("@Kggbh0", kgg);
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            if (dt.Rows.Count == 0)
                return null;
            return new string[] { (string)dt.Rows[0]["Bdzmc"], (string)dt.Rows[0]["Kgsmc"], (string)dt.Rows[0]["Kggbh"] };
        }

        public String getPwd()
        {
            DataTable dt = new DataTable();
            com = GetConnection().CreateCommand();
            com.CommandText = "select pwd from [user]";
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return (String)dt.Rows[0]["pwd"];
        }

        public string getNumber(string src) {

            if (src.Length == 1)
                return "00" + src;
            if (src.Length == 2)
                return "0" + src;
            return src;
        }
        public void Excel2Access()
        {
            OpenFileDialog dlg = new OpenFileDialog();
            //dlg.Filter = "Excel文件(*.xls)|*.xls|(*.xlsx)|*.xlsx";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                string filePath = dlg.FileName;
                //MessageBox.Show(filePath);
                try
                {
                    OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
                    connectStringBuilder.DataSource = filePath.Trim();
                    connectStringBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
                    connectStringBuilder.Add("Extended Properties", "Excel 8.0");
                    using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
                    {
                        DataSet ds = new DataSet();
                        string sql = "Select * from [Sheet1$]";
                        OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
                        cn.Open();
                        using (OleDbDataReader drLiming = cmdLiming.ExecuteReader())
                        {
                            
                            ds.Load(drLiming, LoadOption.OverwriteChanges, new string[] { "Sheet1" });
                            DataTable dt = ds.Tables["Sheet1"];
                            bool exe_result = true;
                            if (dt.Rows.Count > 0)
                            {
                                int count = dt.Rows.Count;
                                for (int i = 0; i < count; i++)
                                {
                                    //MessageBox.Show( dt.Rows[i]["Bdzmc"].ToString() + dt.Rows[i]["Kgsmc"].ToString() + dt.Rows[i]["Kggbh"].ToString());
                                    //写入数据库数据
                                    string Sql = "insert into PositionInfo(Bdzmc,Kgsmc,Kggbh,Bdzbh,Kgsbh,Kggbh0) values('" + dt.Rows[i]["变电站名称"].ToString()
                                        + "','" + dt.Rows[i]["开关室名称"].ToString() + "','" + dt.Rows[i]["开关柜名称"].ToString()
                                        + "','" + getNumber(dt.Rows[i]["变电站编号"].ToString()) + "','" + getNumber(dt.Rows[i]["开关室编号"].ToString())
                                        + "','" + getNumber(dt.Rows[i]["开关柜编号"].ToString()) + "')";
                                    //MessageBox.Show(Sql);
                                    if (!(exe_result=AccessSQLExecute(Sql)))
                                    {
                                        MessageBox.Show("数据导入失败！发生在导入第"+(i+1)+"条数据处，请检查");
                                    }
                                }
                                if (exe_result)
                                {
                                    MessageBox.Show(count+"条数据已经成功导入");
                                }
                               
                            }
                            else
                            {
                                MessageBox.Show("请检查你的Excel中是否存在数据");
                            }
                           
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }
        public bool AccessSQLExecute(string sql)
        {
            try
            {
                OleDbConnection accessConn = new OleDbConnection(sqlAddress);
                accessConn.Open();//打开access数据库
                OleDbCommand comm = new OleDbCommand();
                comm.Connection = accessConn;
                comm.CommandText = sql;
                comm.ExecuteNonQuery();
                comm.Connection.Close();
                accessConn.Close();//关闭access数据库
                return true;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;

            }
        }
        public DataTable GetPortInfoTable()
        {
            DataTable dt = new DataTable();
            com = GetConnection().CreateCommand();
            com.CommandText = "select ID,Bdzmc,Bdzbh,Kgsmc,Kgsbh,Kggbh,Kggbh0 from  [PositionInfo]";
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return dt;
        }
        public void UpdatePortInfo(DataGridViewRow dr)
        {
            com = GetConnection().CreateCommand();
            com.CommandText = String.Format("update [PositionInfo] set Bdzmc=\"{0}\", Kgsmc=\"{1}\" ,Kggbh=\"{2}\" where ID={3}", dr.Cells[1].Value, dr.Cells[2].Value, dr.Cells[3].Value, dr.Cells[0].Value);
            try
            {
                //MessageBox.Show(com.CommandText);
               com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
        }
        public void InsertPortInfo(DataGridViewRow dr)
        {
            com = GetConnection().CreateCommand();
            com.CommandText = String.Format("insert into PositionInfo(Bdzmc,Kgsmc,Kggbh,Bdzbh,Kgsbh,Kggbh0) values('"+ dr.Cells[1].Value
                                        + "','" +  dr.Cells[3].Value + "','" + dr.Cells[5].Value
                                        + "','" +  dr.Cells[2].Value + "','" + dr.Cells[4].Value
                                        + "','" +  dr.Cells[6].Value + "')");
            try
            {
                //MessageBox.Show(com.CommandText);
                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
        }
        public void clearPortInfo()
        {
            com = GetConnection().CreateCommand();
            
            try
            {
                com.CommandText = String.Format("delete from PositionInfo");
                com.ExecuteNonQuery() ;
                com.CommandText = String.Format("alter table PositionInfo alter column[ID] counter(1,1);");
                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
        }
        /*limits={NO2,CO,NO,O3}*/
        public void Insert(double[] limits)
        {
            com = GetConnection().CreateCommand();
            com.CommandText = "insert into limit (NO2,CO,NO,O3) values("+ limits[0]+","+limits[1]+"," +limits[2]+","+limits[3] +")";
            try
            {
                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.WriteLine(com.CommandText);
                sw.Close();
            }
            finally
            {
                Close();
            }
        }

        public void UpdateLimites(int id ,double no2,double co,double no,double o3)
        {
            com = GetConnection().CreateCommand();
            com.CommandText = "UPDATE  [limit] SET  [NO2]="+no2+", [CO]="+co+" , [NO]="+no+" ,[O3]="+o3+"  WHERE [id]="+id;
            try
            {
                //MessageBox.Show(com.CommandText);
                com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
        }
        public DataTable SelectLimits()
        {
            DataTable dt = new DataTable();
            com = GetConnection().CreateCommand();
            com.CommandText = "select * from  limit";
            try
            {
                OleDbDataAdapter sda = new OleDbDataAdapter(com);
                sda.Fill(dt);
                dt.Dispose();
            }
            catch (Exception ex)
            {
                StreamWriter sw = new StreamWriter(System.Environment.CurrentDirectory + "\\log.txt", true, Encoding.Unicode, 4096);
                sw.WriteLine(ex.Message + DateTime.Now.ToLongTimeString());
                sw.Close();
            }
            finally
            {
                Close();
            }
            return dt;
        }
        
    }
}
